package net.bingosoft.common.data;

import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.bingosoft.common.BeanHelper;
import net.bingosoft.common.EncodedProperties;
import net.bingosoft.common.ObjectRegistry;
import net.bingosoft.common.config.Config;
import net.bingosoft.common.data.security.DataSecurityContext;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.util.Assert;

import bingo.security.datasecurity.core.DataSecurityManager;
/**
 * 2007-10-10 下午02:02:05
 * Author:yangdongyu(yangdy@bingosoft.net)
 * ReadMe:读取类路径 sqls 目录下的sql文件钟的sql语句的工具类
 * ChangeLog:
 */
public class SQLProvider {
	private static final Log log = LogFactory.getLog(SQLProvider.class);
	private static final String sqlMapLocation = Config.getString("SQLMAP_LOCATION","classpath*:/sqls/**/*.xml");
	private static Properties sqlMap = EncodedProperties.getInstance(sqlMapLocation,true);
	private static final Pattern paramPattern = Pattern.compile("#((\\.|\\?|\\w|\\:|\\(|\\)|\\[|\\])+)#");
	private static final String DATA_SECURITY_CONTEXT_BEAN_ID = Config.getString("DATA_SECURITY_CONTEXT_BEAN_ID", DataSecurityContext.class.getSimpleName());
	private static final String DATA_SECURITY_MANAGER_BEAN_ID = Config.getString("DATA_SECURITY_MANAGER_BEAN_ID", DataSecurityManager.class.getSimpleName());
	private static DataSecurityContext securityContext ;
	private static String ACTION_PREFIX = Config.getString("ACTION_PREFIX", "Action:");
	private static DataSecurityManager dsm;
	private static final String actionPrefix = "$";
	private static final String actionSurfix = "$";
	
	static {
		if(ObjectRegistry.containsBean(DATA_SECURITY_CONTEXT_BEAN_ID)){
			securityContext = ObjectRegistry.getBean(DATA_SECURITY_CONTEXT_BEAN_ID,DataSecurityContext.class);
		}
		if(ObjectRegistry.containsBean(DATA_SECURITY_MANAGER_BEAN_ID)){
			dsm = ObjectRegistry.getBean(DATA_SECURITY_MANAGER_BEAN_ID,DataSecurityManager.class);
		}
	}
	
	/**
	 * 根据sqlId(key)从sql.xml中取出对应的sql语句,该sql语句支持动态修改
	 * 如果找不到指定key的sql,则返回key值作为sql
	 * @param key sql语句的键值
	 * @return 
	 */
	public static String getSQL(String key){
		return getSQLInternal(key);
	}
	/**
	 * 实现数据访问控制的动作的解析
	 * @param key
	 * @return
	 */
	private static String getSQLInternal(String key){
		String sql = sqlMap.getProperty(key);
		if(StringUtils.isNotEmpty(sql)){
			log.debug("SQL[" + key +"]:" + sql);	
		}else if(sql == null && key.contains(" ")){
			sql = key;
		}
		if(sql == null || "".equals(sql.trim())) return null;
		int start = sql.indexOf(actionPrefix);
		int end = sql.indexOf(actionSurfix, start+1);
		while(start > -1 && end > -1){
			String name = sql.substring(start + 1,end);
			sql = sql.replace(actionPrefix + name + actionSurfix, dsm.resolve(name));
			start = sql.indexOf(actionPrefix);
			end = sql.indexOf(actionSurfix,start+1);
		}
		return sql;
	}
	
	/**
	 * 判断给定的sql语句是否是更新语句,包括插入和更新
	 * @param sql
	 * @return
	 */
	private static boolean isUpdateSQL(String sql){
		if(sql == null || sql.trim().length() == 0) throw new IllegalArgumentException("input param sql can not be null or empty ...");
		String lines [] = sql.split("\n");
		for(String line : lines){
			String trimLine = line.trim();
			if("".equals(trimLine) || trimLine.startsWith("--")){
				continue;
			}
			String lowerSQL = trimLine.toLowerCase();
			return lowerSQL.startsWith("insert")||lowerSQL.startsWith("update");
		}
		return false;
	}
	/**
	 * 解析表达式中的参数,获取动态的查询语句
	 * 动态sql的书写规则:如在ibatis中写法为
	 *          <!-- 状态 -->
	 *			<isNotEmpty prepend="and" property="status">
	 *				c.status = #status#
	 *			</isNotEmpty>
	 *			在动态sql中可以写成 {?and c.status = #status#}
	 *			如果在参数对象中存在status属性并且属性值(paramValue)不为空(paramValue != null && !((paramValue instanceof String) && ((String)paramValue).trim().equals("")))
	 *			则该字句将被解析成 and c.status = ? ,?占位符对应的参数会被按出现顺序放入DynamicSQL的参数(params)数组中,如果参数值为空则该字句将被忽略
	 *			在ibatis中写法为:
	 *			<isNotEmpty prepend="and" property="status">
	 *				c.status = $status$
	 *			</isNotEmpty>
	 *			在动态sql中等价写法为 {@and c.status = #status#}
	 *			如果在参数对象中存在status属性并且属性值不为空,则该语句将被解析成
	 *			 and c.status = statusValue,其中statusValue为参数paramBean中status值的字符串表示,如果参数值为空则该字句将被忽略,
	 *			在动态语句中不许区分 isNotEmpty 和 isNotNull 
	 * @param sqlKey sql语句在sql.xml中的键值
	 * @param paramBean 参数Bean,可以是Map
	 * @return
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public static DynamicSQL getDynamicSQL(String sqlKey,Object paramBean) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		String sql = getSQL(sqlKey);
		//判断语句是否是插入语句
		boolean isUpdateSQL = isUpdateSQL(sql);
		StringBuilder sqlBuffer = new StringBuilder();
		String param = "";
		boolean hasParamStartChars = false;
		boolean isUsingPlaceholder = false;
		List <Object>paramValues = new  ArrayList<Object> ();
		for(int i=0;i<sql.length();i++){
			char c = sql.charAt(i);
			if(hasParamStartChars){
				if(c == '}'){
					//如果符合结束条件
					hasParamStartChars = false;
					String subClase = param.substring(2);
					parseParamClause(subClase,isUsingPlaceholder,sqlBuffer,paramBean,paramValues,isUpdateSQL);
					param = "";
				}else{
					/* 参数名不支持除, : [ . _ ] { }外的非字母字符 */
					if( c != ':' || c != ',' || c != '[' || c != ']' || c!='.' || c!='_' || c!='{' || c!= '}' || (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z')){
						param += String.valueOf(c);
						if(i == sql.length() - 1){
							sqlBuffer.append(param);
						}                        
					}else{
						sqlBuffer.append(param);
						sqlBuffer.append(c);
						param = "";
						hasParamStartChars = false;
					}
				}
			}else{
				//if match param start chars {@ or {?
				if(c == '{' && i < sql.length() - 1 && ( sql.charAt(i+1) == '@' || sql.charAt(i+1) == '?')){
					char nextChar = sql.charAt(i+1);                    
					hasParamStartChars = true;
					isUsingPlaceholder = (nextChar == '@' ? false : true);
					param += "{" + nextChar;
					i++;
				}else{
					sqlBuffer.append(c);
				}
			}
		}
		DynamicSQL result = new DynamicSQL(sqlBuffer.toString(),paramValues.toArray());
		if(log.isDebugEnabled()){
			log.debug("DynamicSQL[" + sqlKey +"]:" + result.getSql() + "\n\tDynamicSQL Params :[" + BeanHelper.join(result.getParams(),",")+"]");
		}
		return result;
	}

    /**
     * 解析查询语句
     * @param paramClause 待解析的查询子句
     * @param usePlaceHolder 是否使用了?占位符
     * @param sqlBuffer sql语句的buffer
     * @param paramBean 提供参数的bean
     * @param paramList 原有的参数列表
     * @param isUpdateSQL 该语句是否是插入语句
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
	private static void parseParamClause (String paramClause,boolean usePlaceHolder,StringBuilder sqlBuffer,Object paramBean,List<Object> paramList,boolean isUpdateSQL) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		Matcher paramMatcher = paramPattern.matcher(paramClause);		
		List <Object> tempParamList = new ArrayList<Object>();
		boolean ignoreThisClause = false;
		int start = 0;
		while(paramMatcher.find(start)){
			String propertyName = paramMatcher.group(1);			
			//获取参数值
			Object paramValue = getParamValue(paramBean,propertyName,paramList);
			boolean isParamExists = (paramValue != null && !((paramValue instanceof String) && ((String)paramValue).trim().equals("")));
			//在where 语句之后忽略空值子句
			if(!isParamExists && isUpdateSQL && !sqlBuffer.toString().toLowerCase().contains("where")){//对于插入语句,如果参数不存在,插入空值
				isParamExists = true;
			}
			if(!isParamExists){//只要有一个参数不存在,我们就忽略掉这条sql语句
				ignoreThisClause = true;
				break;
			}else{
				if(usePlaceHolder){//使用了?占位符
					paramClause = Pattern.compile(paramMatcher.group(), Pattern.LITERAL).matcher(paramClause).replaceFirst(" ? ");
					tempParamList.add(paramValue);
				}else{
					paramClause = Pattern.compile(paramMatcher.group(), Pattern.LITERAL).matcher(paramClause).replaceFirst(BeanHelper.converToString(paramValue));
				}
			}
			start = paramMatcher.end();
		}
		if(!ignoreThisClause){
			sqlBuffer.append(paramClause);
			if(usePlaceHolder){
				paramList.addAll(tempParamList);
			}
		}else{
			tempParamList = null;
		}
	}
	/**
	 * 获取参数的值
	 * @param paramBean
	 * @param paramName
	 * @param paramList
	 * @return
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	private static Object getParamValue(Object paramBean,String paramName,List<Object> paramList) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
		Object paramValue = null;
		if(paramName.startsWith(ACTION_PREFIX)){//用于数据权限控制的动作
			Object result = securityContext.resolve(paramName.substring(ACTION_PREFIX.length()));
			if(result instanceof DynamicSQL){
				DynamicSQL dynaRule = (DynamicSQL)result;
				Object params[] = dynaRule.getParams();
				if(params.length > 0){
					paramList.addAll(Arrays.asList(dynaRule.getParams()));
				}
				return dynaRule.getSql();
			}else{
				return result;
			}
		}else if(paramBean == null){
			log.warn("paramBean is null ...");
		}else if(paramBean instanceof Map){
			paramValue = ((Map)paramBean).get(paramName);
		}else if(paramBean instanceof Object[]){
			paramValue = ((Object[])paramBean)[paramList.size()];
		}else{
			paramValue = BeanHelper.getRawProperty(paramBean, paramName);
		}
		return paramValue;
	}
	/**
	 * 获取分页查询的sql
	 * @param sql
	 * @param hasOffset
	 * @return
	 */
	public static String getPageSQL(String sql) {
		sql = sql.trim();
		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
		pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
		pagingSelect.append(sql);
		pagingSelect.append("\n ) row_ where rownum <= ?) where rownum_ > ?");
		return pagingSelect.toString();
	}
	/**
	 * 获取查询总记录数的SQL语句
	 * @param sql
	 * @return
	 */
	public static String getCountSQL(String sql){
		return getCountSQL(sql, false);
	}
	
	/**
	 * 获取查询总记录数的SQL语句
	 * @param sql
	 * @param optimize 是否对SQL 语句进行优化
	 * @return
	 */
	public static String getCountSQL(String sql,boolean optimize){
		if(optimize){
			String sqlWithoutOrder = removeOrders(sql);
			String countSQL = removeSelect(sqlWithoutOrder);
			if(countSQL.substring(4).toLowerCase().contains("from")){
				countSQL = " select count (1) from (" + sqlWithoutOrder + "\n)";
			}else{
				countSQL = " select count (1) " + countSQL;
			}
			return countSQL;
		}else{
			return " select count (1) from (\n" + sql + "\n)";
		}
	}
	/**
	 * 去除sql或hql的select 子句，未考虑union的情况,用于pagedQuery.
	 */
	public static String removeSelect(String sql) {
		Assert.hasText(sql);
		String lowerSQL = sql.toLowerCase();
		int beginPos = lowerSQL.indexOf("from");
		Assert.isTrue(beginPos != -1, " sql : " + sql + " must has a keyword 'from'");
		int endPos = lowerSQL.lastIndexOf("from");
		return (beginPos != endPos) ? sql:sql.substring(beginPos);
	}

	/**
	 * 去除sql或hql的orderby 子句，用于pagedQuery.
	 */
	public static String removeOrders(String hql) {
		Assert.hasText(hql);
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	public static StringBuffer appendLikeClause(StringBuffer srcSQL,String beanOrAliasName, String propertyOrColumnName, String propertyOrColumnNameValue) {
		if (StringUtils.isNotEmpty(propertyOrColumnNameValue)) {
			srcSQL.append(" and ").append(beanOrAliasName).append("." ).append(propertyOrColumnName).append("  like '%").append(propertyOrColumnNameValue).append("%' ");
		}
		return srcSQL;
	}

	public static StringBuffer appendEqualClause(StringBuffer srcSQL,String beanOrAliasName, String propertyOrColumnName, String propertyOrColumnNameValue) {
		if (StringUtils.isNotEmpty(propertyOrColumnNameValue)) {
			srcSQL.append(" and ").append(beanOrAliasName).append(".").append(propertyOrColumnName).append("  = '").append(propertyOrColumnNameValue).append("' ");
		}
		return srcSQL;
	}

	public static StringBuffer appendEqualIntClause(StringBuffer srcSQL,String beanOrAliasName, String propertyOrColumnName,
			String propertyOrColumnNameValue) {
		if (StringUtils.isNotEmpty(propertyOrColumnNameValue)) {
			srcSQL.append(" and ").append(beanOrAliasName).append(".").append(propertyOrColumnName).append("  = ").append(propertyOrColumnNameValue).append(" ");
		}
		return srcSQL;
	}

	public static StringBuffer appendGreatClause(StringBuffer srcSQL,String beanOrAliasName, String propertyOrColumnName, String propertyOrColumnNameValue) {
		if (StringUtils.isNotEmpty(propertyOrColumnNameValue)) {
			srcSQL.append(" and ").append(beanOrAliasName).append(".").append(propertyOrColumnName).append("  > ").append(propertyOrColumnNameValue);
		}
		return srcSQL;
	}

	public static StringBuffer appendLessClause(StringBuffer srcSQL,String beanOrAliasName, String propertyOrColumnName, String propertyOrColumnNameValue) {

		if (StringUtils.isNotEmpty(propertyOrColumnNameValue)) {
			srcSQL.append(" and ").append(beanOrAliasName).append(".").append(propertyOrColumnName).append("  < ").append(propertyOrColumnNameValue);
		}
		return srcSQL;
	}
	/**
	 * 扩展一个in 子句
	 * @param srcSQL
	 * @param beanOrAliasName
	 * @param propertyOrColumnName
	 * @param propertyOrColumnNameValue
	 * @return
	 */
	public static StringBuffer appendInClause(StringBuffer srcSQL,String beanOrAliasName, String propertyOrColumnName, String propertyOrColumnNameValue) {
		if (StringUtils.isNotEmpty(propertyOrColumnNameValue)) {
			srcSQL.append(" and ").append(beanOrAliasName).append(".").append(propertyOrColumnName).append("  in (").append(propertyOrColumnNameValue ).append(")");
		}
		return srcSQL;
	}
	/**
	 * 为指定表生成动态插入语句
	 * @param tableName 待插入数据的表名称
	 * @param fields 表所包含的字段名,要求列表中第一个字段为表的主键字段
	 * @param paramBeanOrMap 包含参数的pojo或map
	 * @param sequenceName 用来生成主键的序列名称
	 * @return
	 */
	public static DynamicSQL generateInsertSQL(String tableName,List <String> fields,Object paramBeanOrMap,String sequenceName){
		StringBuffer sb = new StringBuffer(150);
		sb.append("INSERT INTO ").append(tableName).append("(");
		StringBuffer valueBuffer = new StringBuffer(150);
		List<Object> paramsList = new ArrayList<Object>();
		boolean isFirstField = true;
		for(String field : fields){
			Object value = null;
			try {
				value = getParamValue(paramBeanOrMap, field,null);
			} catch (Exception e) {
				log.warn("从参数对象获取属性出错,忽略该属性插入...",e);
				continue;
			}
			if(value != null ||isFirstField){
				if(isFirstField){
					sb.append(field);
					valueBuffer.append(" VALUES(").append(sequenceName).append(".nextval");
				}else{
					sb.append(",").append(field);
					valueBuffer.append(",?");
					paramsList.add(value);
				}
			}
			isFirstField = false;
		}
		sb.append(")").append(valueBuffer).append(")");
		return new DynamicSQL(sb.toString(),paramsList.toArray());
	}
	/**
	 * 为指定表生成动态更新语句
	 * @param tableName 待更新数据的表名称
	 * @param fields 表所包含的字段名,要求列表中第一个字段为表的主键字段
	 * @param paramBeanOrMap 包含参数的map或bean
	 * @param keyValue 主键字段的值,用来定位要更新的记录
	 * @return
	 */
	public static DynamicSQL generateUpdateSQL(String tableName,List <String> fields,Object paramBeanOrMap,String keyValue){
		StringBuffer sb = new StringBuffer(150);
		sb.append("UPDATE ").append(tableName).append(" T ");
		String keyField = fields.get(0);
		List<Object> paramsList = new ArrayList<Object>();
		boolean isFirstField = true;
		for(String field : fields){
			if(field.equals(keyField)) continue;
			Object value = null;
			try {
				value = getParamValue(paramBeanOrMap, field,null);
			} catch (Exception e) {
				log.warn("从参数对象获取属性["+field+"]出错,忽略该属性更新...",e);
				continue;
			}
			if(value != null ||isFirstField){
				if(isFirstField){
					sb.append("SET T.").append(field).append(" = ? ");
				}else{
					sb.append(",").append(" T.").append(field).append(" = ? ");
				}
				paramsList.add(value);
			}
			isFirstField = false;
		}
		sb.append(" WHERE T.").append(keyField).append(" = ").append("'").append(keyValue).append("'");
		return new DynamicSQL(sb.toString(),paramsList.toArray());
	}

	/**
	 * 2007-12-5 下午08:28:44
	 * Author:yangdongyu(yangdy@bingosoft.net)
	 * ReadMe:动态sql对象,包含一个sql和sql中?占位符对应的参数数组
	 * ChangeLog:
	 */
	public static class DynamicSQL{
		private String sql;
		private Object[] params;
		public DynamicSQL(String sql, Object[] params) {
			this.sql = sql;
			this.params = params;
		}
		public Object[] getParams() {
			return params;
		}
		public void setParams(Object[] params) {
			this.params = params;
		}
		public String getSql() {
			return sql;
		}
		public void setSql(String sql) {
			this.sql = sql;
		}

		public String toString(){
			return "SQL : "+this.sql+"\nPARAMS : "+ BeanHelper.join(this.params,",");
		}

	}
}
